import pymysql
from ToDos.todo_app_with_mysql.todo import Todo
class TodoStorageWithMySQL:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.113.132',database='test',user='root',password='root@123ABC',autocommit=True)

    def init_db(self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('''
CREATE TABLE IF NOT EXISTS todos(
id int PRIMARY key auto_increment,
title VARCHAR(40) not null DEFAULT '',
completed VARCHAR(40) DEFAULT ('False'))

            ''')
    def list_todos(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('select * from todos ')
            data = cursor.fetchall()
            self.todo_list=[]
            for row in data:
                self.todo_list.append(self.covert_row_todos(row))
            return self.todo_list

    # 返回未完成的列表数据
    def unfinished_todo(self):
        self.list_todos()
        unfinished_todos = []
        for temp in self.todo_list:
            if temp.completed == 'False':

                unfinished_todos.append(temp)
        return unfinished_todos

    # 返回已完成的列表数据
    def comepleted_todo(self):
        self.list_todos()
        completed_todos = []
        for temp in self.todo_list:
            if temp.completed == 'True':

                completed_todos.append(temp)
        return completed_todos

    def covert_row_todos(self,row):
        return Todo(row[1], row[0], row[2])


    def create(self, title):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('insert into todos(title) values(%s)',(title,))
            cursor.execute('select last_insert_id()')
            (id,)=cursor.fetchone()
            return Todo(title,id=id)
            #返回结果条数
            # res=cursor.execute("select * from students")
            #返回第一条数据 元组
            # data1=cursor.fetchone()
            #返回所有数据 元组套元组
            # data=cursor.fetchall()

    def mark_completed(self, id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("update todos set completed='True'  where id='%s'",(id,))
            todo = self.get_todo_byid(id)
            return todo

    def get_todo_byid(self, id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('select * from todos where id=(%s)',(id,))
            (todo,)=cursor.fetchall()
            # print(self.covert_row_todos(todo))
            if todo is not None:
                return self.covert_row_todos(todo)
            else:
                return None

    def delete(self,id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('delete from todos where id=(%s)',(id,))


manger=TodoStorageWithMySQL()
# manger.create('haha')
manger.init_db()
# manger.list_todos()
# manger.mark_completed(1)
# manger.unfinished_todo()
# manger.comepleted_todo()